package com.example.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demo.mapper.entity.Student;
import com.example.demo.mapper.entity.StudentView;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
public interface StudentManageMapper extends BaseMapper<Student> {

    @Select("<script>" +
            "SELECT * " +
            " FROM `student_view`" +
            " ORDER BY id ASC" +
            "</script>")
    List<StudentView> getStudentList();

    @Select("SELECT school_id FROM `sys_user` WHERE user_id = #{stkaaaId}")
    int getSchoolId(int stkaaaId);

    @Select("SELECT * FROM `student` WHERE school_id = #{schoolId}")
    List<Student> getStudentListById(int schoolId);

    @Select("SELECT count(1) FROM student WHERE number = #{number}")
    int countByStudentNum(String number);

    @Delete("DELETE FROM `student` WHERE number = #{studentNumber}")
    void deleteStudent(String studentNumber);

    @Select("SELECT college_id FROM `college` WHERE college_name = #{college}")
    Integer selectCollege(String college);

    @Select("SELECT major_id FROM `major` WHERE major_name = #{major}")
    Integer selectMajor(String major);

    @Insert("INSERT INTO student(number, name, gender, birthday, identity_card, native_place, nationality, " +
            "politics_status, phone, email, college_id, major_id, clazz, dormitory, room_number, " +
            "bed_number, contact, contact_phone, avatar, password, score, state, nickname, bareheaded_photo,enrollment_id) " +
            "VALUES(#{number}, #{name}, #{gender}, #{birthday}, #{identityCard}, #{nativePlace}, #{nationality}, " +
            "#{politicsStatus}, #{phone}, #{email}, #{collegeId}, #{majorId}, #{clazz}, #{dormitory}, " +
            "#{roomNumber}, #{bedNumber}, #{contact}, #{contactPhone}, #{avatar}, #{password}, #{score}, #{state}, " +
            "#{nickname}, #{bareheadedPhoto}, #{enrollmentId})")
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    void addStudent(Student newStudent);

    @Delete("<script>" +
            "DELETE FROM student" +
            " WHERE id IN" +
            " <foreach collection='list' index='index' open='(' separator=',' close=')' item='studentList' >" +
            "   #{studentList.id}" +
            " </foreach>" +
            "</script>")
    void deleteStudentList(List<StudentView> studentList);

    @Select("<script>" +
            "SELECT * " +
            " FROM student_view" +
            "<where>" +
            "  <if test='example.number != null and example.number != \"\" '>" +
            "     AND number like CONCAT('%', #{example.number}, '%') " +
            "  </if>" +
            "  <if test='example.name != null and example.name != \"\" '>" +
            "     AND name like CONCAT('%', #{example.name}, '%') " +
            "  </if>" +
            "  <if test='example.collegeName != null and example.collegeName != \"\" '>" +
            "     AND college_name like CONCAT('%', #{example.collegeName}, '%') " +
            "  </if>" +
            "  <if test='example.majorName != null and example.majorName != \"\" '>" +
            "     AND major_name like CONCAT('%', #{example.majorName}, '%') " +
            "  </if>" +
            "</where>" +
            "ORDER BY number DESC " +
            "</script>")
    List<StudentView> searchByExample(StudentView example);
    @Select("SELECT * FROM student WHERE number = #{number}")
    Student selectStudentByNumber(String number);
/*student(number, name, gender, birthday, identity_card, native_place, nationality, " +
            "politics_status, phone, email, college_id, major_id, clazz, dormitory, room_number, " +
            "bed_number, contact, contact_phone, avatar, password, score, state, nickname, bareheaded_photo,enrollment_id) " +
            "VALUES(#{number}, #{name}, #{gender}, #{birthday}, #{identityCard}, #{nativePlace}, #{nationality}, " +
            "#{politicsStatus}, #{phone}, #{email}, #{collegeId}, #{majorId}, #{clazz}, #{dormitory}, " +
            "#{roomNumber}, #{bedNumber}, #{contact}, #{contactPhone}, #{avatar}, #{password}, #{score}, #{state}, " +
            "#{nickname}, #{bareheadedPhoto}, #{enrollmentId})*/
    @Update("UPDATE student SET number = #{number},name = #{name},gender = #{gender},birthday= #{birthday}," +
            "identity_card = #{identityCard}, native_place = #{nativePlace}, nationality = #{nationality}," +
            "politics_status = #{politicsStatus}, phone = #{phone}, email = #{email}, college_id = #{collegeId}, " +
            "major_id = #{majorId}, clazz = #{clazz}, dormitory = #{dormitory}, room_number = #{roomNumber}," +
            "bed_number = #{bedNumber}, contact = #{contact}, contact_phone = #{contactPhone}, avatar = #{avatar}, " +
            "password = #{password}, score = #{score}, state = #{state}, nickname = #{nickname}, " +
            "bareheaded_photo = #{bareheadedPhoto},enrollment_id = #{enrollmentId}" +
            " WHERE id = #{id}")
    void updateStudent(Student student);
}
